package com.ljx.excel.service.impl;

import cn.hutool.core.util.RandomUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ljx.excel.component.UserRowMapper;
import com.ljx.excel.domain.User;
import com.ljx.excel.mapper.UserMapper;
import com.ljx.excel.service.TestService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;

/**
 * @Author: LiJunXi
 * @Description:
 * @Date: 2025-08-18  21:27
 */
@Slf4j
@Service
public class TestServiceImpl implements TestService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private UserMapper userMapper;

    public static final int BATCH_COUNT = 200000;
    public static final int SHEET_COUNT = 1000000;
    public static final int THREAD_POOL_CORE_SIZE = Runtime.getRuntime().availableProcessors();
    public static final int THREAD_POOL_MAX_SIZE = THREAD_POOL_CORE_SIZE * 2;

    // 线程池 - 使用有界队列防止内存溢出
    private static final ThreadPoolExecutor executor = new ThreadPoolExecutor(
            THREAD_POOL_CORE_SIZE,
            THREAD_POOL_MAX_SIZE,
            60L, TimeUnit.SECONDS,
            new ArrayBlockingQueue<>(100),
            new ThreadPoolExecutor.CallerRunsPolicy()
    );
    private final UserRowMapper userRowMapper = new UserRowMapper();

    @Override
    public void export(HttpServletResponse response, Integer type) {
        try {
            log.info("------核心线程数：" + THREAD_POOL_CORE_SIZE + "------");
            log.info("------最大线程数：" + THREAD_POOL_MAX_SIZE + "------");
            long startReadTime = System.currentTimeMillis();
            log.info("------开始导出Excel时间:" + startReadTime + "ms------");

            // 获取 JVM 当前内存状态
            Runtime runtime = Runtime.getRuntime();
            long beforeUsedMemory = runtime.totalMemory() - runtime.freeMemory();

            // 设置响应头（文件名、格式）
            setExcelResponseProp(response, "百万用户数据导出");
            // 使用EasyExcel导出
            OutputStream outputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream, User.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动列宽
                    .inMemory(false)
                    .autoCloseStream(false) // 禁止自动关闭流
                    .build();

            if (type == 1) {
                exportExcel1(excelWriter);
            } else if (type == 2) {
                exportExcel2(excelWriter);
            } else if (type == 3) {
                exportExcel3(excelWriter);
            }

            // 关闭资源
            excelWriter.finish();
            outputStream.flush();

            long endReadTime = System.currentTimeMillis();
            long afterUsedMemory = runtime.totalMemory() - runtime.freeMemory();
            long memoryUsed = afterUsedMemory - beforeUsedMemory;


            log.info("------结束导出Excel时间:" + endReadTime + "ms------");
            log.info("------总共耗时:" + (endReadTime - startReadTime) / 1000 + "秒------");
            log.info("------导出数据内存使用：" + memoryUsed / (1024 * 1024) + " MB------");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void exportExcelTest(ExcelWriter excelWriter) {
        WriteSheet userSheet = EasyExcel.writerSheet("用户数据")
                .build();

        excelWriter.write(getUserListAll(), userSheet);
    }


    /**
     * MyBatis版本数据分批查询
     *
     * @param excelWriter
     */
    private void exportExcel1(ExcelWriter excelWriter) {
        int sheetIndex = 1;
        Long beforeUId = null;
        WriteSheet userSheet = null;
        boolean hasNext = true;

        while (hasNext) {
            // 定义Sheet并写入数据
            userSheet = EasyExcel.writerSheet(String.format("用户数据_%d", sheetIndex++))
                    .build();
            for (int i = 0; i < SHEET_COUNT / BATCH_COUNT; i++) {
                List<User> users = userMapper.selectUserListBatch(beforeUId, BATCH_COUNT);
                int userSize = users.size();
                beforeUId = users.get(userSize - 1).getId();

                log.info(String.format("查询完%d个的%d批数据", sheetIndex - 1, i + 1));
                excelWriter.write(users, userSheet);
                log.info(String.format("导入完%d个的%d批数据", sheetIndex - 1, i + 1));

                if (userSize != BATCH_COUNT) {
                    hasNext = false;
                    break;
                }
            }

        }
    }


    /**
     * JDBC版本数据分批查询
     *
     * @param excelWriter
     */
    private void exportExcel2(ExcelWriter excelWriter) {
        int sheetIndex = 1;
        Long beforeUId = null;
        WriteSheet userSheet = null;
        boolean hasNext = true;

        while (hasNext) {
            // 定义Sheet并写入数据
            userSheet = EasyExcel.writerSheet(String.format("用户数据_%d", sheetIndex++))
                    .build();
            for (int i = 0; i < SHEET_COUNT / BATCH_COUNT; i++) {
                List<User> users = getUserListBatch(beforeUId);
                int userSize = users.size();
                beforeUId = users.get(userSize - 1).getId();

                log.info(String.format("查询完%d个的%d批数据", sheetIndex - 1, i + 1));
                excelWriter.write(users, userSheet);
                log.info(String.format("导入完%d个的%d批数据", sheetIndex - 1, i + 1));

                if (userSize != BATCH_COUNT) {
                    hasNext = false;
                    break;
                }
            }
        }
    }


    /**
     * 测试MyBatis效率远高于JDBC版本，原因为如下
     * - MyBatis使用线程池进行管理（最重要的一点）
     * - MyBatis使用预编译语句和尽可能的复用
     * - MyBatis自动实现了一条SQL执行的全部流程：自动获取连接、创建预编译语句、设置参数、执行查询、最重要的是自动将ResultSet结果集映射成User对象列表，最后自动、安全地关闭所有资源
     * MyBatis版本数据多线程分批查询
     *
     * @param excelWriter
     */
    private void exportExcel3(ExcelWriter excelWriter) {
        int sheetIndex = 1;
        Long beforeUId = null;
        WriteSheet userSheet = null;
        boolean hasNext = true;

        // 使用CompletableFuture执行异步任务
        List<CompletableFuture<Void>> futures = new ArrayList<>();

        while (hasNext) {
            // 定义Sheet并写入数据
            userSheet = EasyExcel.writerSheet(String.format("用户数据_%d", sheetIndex++))
                    .build();
            for (int i = 0; i < SHEET_COUNT / BATCH_COUNT; i++) {
                List<User> users = userMapper.getUserListBatchOnlyId(beforeUId, BATCH_COUNT);
                int userSize = users.size();
                beforeUId = users.get(userSize - 1).getId();
                log.info(String.format("查询完%d个的%d批ID列表数据", sheetIndex - 1, i + 1));

                int finalSheetIndex = sheetIndex;
                WriteSheet finalUserSheet = userSheet;
                Long finalBeforeUId = beforeUId;
                int finalI = i;
                CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
                    List<User> findUsers = userMapper.selectUserListBatch(finalBeforeUId, BATCH_COUNT);
                    log.info(String.format("查询完%d个的%d批数据", finalSheetIndex - 1, finalI + 1));
                    synchronized (excelWriter) {
                        excelWriter.write(findUsers, finalUserSheet);
                    }
                    log.info(String.format("导入完%d个的%d批数据", finalSheetIndex - 1, finalI + 1));
                }, executor);
                futures.add(future);

                if (userSize != BATCH_COUNT) {
                    hasNext = false;
                    break;
                }
            }
        }

        log.info("等待所有任务完成");
        try {
            CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
            log.info("所有任务执行完成");
        } catch (Exception e) {
            log.info("等待任务完成时被中断" + e.getMessage());
            Thread.currentThread().interrupt();
        }
    }


    private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }


    private List<User> getUserListAll() {
        String sql = "SELECT id, username, sex, introduce, ip, create_time, update_time FROM user";

        return jdbcTemplate.query(sql, userRowMapper);
    }

    private List<User> getUserListBatch(Long id) {
        String sql;
        Object[] params;
        if (id != null) {
            sql = "SELECT id, username, sex, introduce, ip, create_time, update_time FROM user where id > ? ORDER BY id ASC LIMIT ?";
            params = new Object[]{id, BATCH_COUNT};
        } else {
            sql = "SELECT id, username, sex, introduce, ip, create_time, update_time FROM user ORDER BY id ASC LIMIT ?";
            params = new Object[]{BATCH_COUNT};
        }
        return jdbcTemplate.query(sql, params, userRowMapper);
    }


    private List<User> getUserListTop100() {
        String sql = "SELECT id, username, sex, introduce, ip, create_time, update_time FROM user limit 100";

        return jdbcTemplate.query(sql, userRowMapper);
    }

    private List<User> getUserCount() {
        String sql = "SELECT count(*) FROM user";

        return jdbcTemplate.query(sql, userRowMapper);
    }

    private int getUserListBatchCount(Long id) {
        String sql;
        Object[] params;
        if (id != null) {
            sql = "SELECT count(*) FROM user where id > ? ORDER BY id ASC";
            params = new Object[]{id, BATCH_COUNT};
        } else {
            sql = "SELECT count(*) FROM user ORDER BY id ASC";
            params = new Object[]{BATCH_COUNT};
        }
        return jdbcTemplate.queryForObject(sql, params, Integer.class);
    }

}
